#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb
import sys

DB_PREFIX = "en_"
TABLE_PAGE = DB_PREFIX + "page"
TABLE_CATEGORYLINKS = DB_PREFIX + "categorylinks"
TABLE_INFOBOX = DB_PREFIX + "infobox"
TABLE_INFOBOX_CATEGORIES = DB_PREFIX + "infobox_categories"
ERROR_DUBLICATE_ENTRY = 1062
#TOP_LEVEL_INFOBOX = (191079, "Википедия:Шаблоны-карточки")

TOP_LEVEL_INFOBOX_CATEGORY = (3108204, "Infobox_templates")
#TOP_LEVEL_INFOBOX_CATEGORY = (191079, "Википедия:Шаблоны-карточки")
#TOP_LEVEL_INFOBOX_CATEGORY = ("2600760", "Шаблоны-карточки:Культура_и_искусство")
NAMESPACE_CATEGORY = "14"
NAMESPACE_TEMPLATE = "10"

def quoted(string):
	return "\"" + string + "\""
#TODO: choose between ' and "

#return page.page_id, page.page_title of infobox from category_name category
def get_infobox(cursor, category, page_type, namespace):
	query = "SELECT page_id, page_title \
				FROM " + TABLE_PAGE + " \
				INNER JOIN ( \
					SELECT cl_from \
					FROM " + TABLE_CATEGORYLINKS + " \
					WHERE cl_to = " + quoted(category[1]) + " \
					AND cl_type = " + page_type + " \
				) as p \
				WHERE " + TABLE_PAGE + ".page_id = p.cl_from \
					AND " + TABLE_PAGE + ".page_namespace = " + namespace 
	try:
		cursor.execute(query) 
		result = cursor.fetchall()
	except MySQLdb.Error, e:
		print "EXEPTION in get_infobox"
		print "\tinfobox id = ", category[0]
		print "\tinfobox title = ", category[1]
		#delete unnecessary spaces for pretty report
		query = ' '.join(query.split())
		print "\tquery = ", query
		raise

	return result	





def write_to_db(cursor, table_name, values):
	query = "INSERT INTO " + table_name + \
				" VALUES ( " +  ", ".join(values) + " )"
	try:
		cursor.execute(query)
	except MySQLdb.Error, e:
		#it may be OK if dublicate. let it be processed higher
		if e.args[0] == ERROR_DUBLICATE_ENTRY:
			raise
		
		#show some error environment	
		print "EXEPTION in write_to_db"
		print "values = ", values
		#delete unnecessaty spaces for pretty report
		query = ' '.join(query.split())
		print "query = ", query
		raise




#recursively search for infobox in subcategories and write them to db
#TABLE_INFOBOX table
def infobox_search_and_save(cursor, category, level):
#category is tuple (page.page_id, page.page_title)

	try:
		#write category id to db
		#throw exception with ERROR_DUBLICATE_ENTRY code if record already exist
		try:	
			write_to_db(cursor, TABLE_INFOBOX_CATEGORIES, [str(category[0])]);
		except MySQLdb.Error, e:
			#it`s OK if dublicate. Do not need to go deeper
			if e.args[0] == ERROR_DUBLICATE_ENTRY:
				print "  STOP ON:", category[1]
				return
			else:
				raise

		print category[1] #report

		#get all pages	
		pages = get_infobox(cursor, category, quoted("page"), NAMESPACE_TEMPLATE)
		for page in pages:
			try:
				write_to_db(cursor, TABLE_INFOBOX, [str(page[0])]);
			except MySQLdb.Error, e:
				#it`s OK if dublicate. Do not need to write this one
				if e.args[0] != ERROR_DUBLICATE_ENTRY:
					raise #but other exception are bad
		
		#get all subcategories
		subcategories = get_infobox(cursor, category, quoted("subcat"), NAMESPACE_CATEGORY)
		for sub in subcategories:
			infobox_search_and_save(cursor, sub, level + 1)

	except MySQLdb.Error, e:
		print "EXEPTION in infobox_search_and_save"
		print "\tcategory id = ", category[0]
		print "\tcategory title = ", category[1]
		raise



try:
	conn = MySQLdb.connect (host = "localhost",
									user = "alex",
									db = "ruwiki")
	conn.set_character_set('utf8')
	cursor = conn.cursor()

	#truncate tables if needed
	cursor.execute("TRUNCATE TABLE " + TABLE_INFOBOX);
	cursor.execute("TRUNCATE TABLE " + TABLE_INFOBOX_CATEGORIES);

	infobox_search_and_save(cursor, TOP_LEVEL_INFOBOX_CATEGORY, 0)

except MySQLdb.Error, e:
	print "Error %d: %s" % (e.args[0], e.args[1])
	sys.exit (1)



cursor.close ()
conn.close ()
